OpenEdge Data Management:<br />SQL Development OLDROW and NEWROW objects: passing values to triggers

The OLDROW and NEWROW objects allow SQL to pass row values as input parameters to the stored procedure in a trigger that executes once for each affected row. If the CREATE TRIGGER statement contains the REFERENCING clause, the SQL server implicitly instantiates an OLDROW or NEWROW object (or both, depending on the arguments to the REFERENCING clause) when it creates the Java class.

This allows the Java code in the snippet to use the getValue method of those objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables:

The OLDROW object contains values of a row as it exists in the database before an update or delete operation. It is instantiated when triggers specify an UPDATE...REFERENCING OLDROW or DELETE...REFERENCING OLDROW clause. It is meaningless and not available for insert operations.

The NEWROW object contains values of a row as specified in an INSERT or UPDATE statement. It is instantiated when triggers specify an UPDATE...REFERENCING NEWROW or INSERT...REFERENCING NEWROW clause. It is meaningless and not available for delete operations.

UPDATE is the only triggering statement that allows both NEWROW and OLDROW in the REFERENCING clause.

Triggers use the OLDROW.getValue and NEWROW.getValue methods to assign a value from a row being modified to a procedure variable. The format and arguments for getValue are the same as in other OpenEdge SQL Java classes:

Syntax
getValue ( col_num , sql_data_type ) ;

Specifies the integer column number of the affected row. getValue retrieves the value in the column denoted by col_num. 1 denotes the first column of the result set, 2 denotes the second, n denotes the nth.

Specifies the corresponding SQL data type. For a complete list of appropriate data types, refer to Table 9–2.

Example 9–22 shows an excerpt from a trigger that uses getValue to assign values from both OLDROW and NEWROW objects.

Example 9–22: Trigger assigning OLDROW and NEWROW values
CREATE TRIGGER BUG_UPDATE_TRIGGER AFTER UPDATE OF STATUS, PRIORITY ON BUG_INFO REFERENCING OLDROW, NEWROW FOR EACH ROW IMPORT import java.sql.* ; BEGIN try { // column number of STATUS is 10 String old_status, new_status; old_status = (String) OLDROW.getValue(10, CHAR); new_status = (String) NEWROW.getValue(10, CHAR); if ((old_status.CompareTo("OPEN") == 0) && (new_status.CompareTo("FIXED") == 0)) { // If STATUS has changed from OPEN to FIXED // increment the bugs_fixed_cnt by 1 in the // row corresponding to current month // and current year SQLIStatement update_stmt ( " update BUG_STATUS set bugs_fixed_cnt = bugs_fixed_cnt + 1 " " where month = ? and year = ?" ); . . .

Example 9–22: Trigger assigning OLDROW and NEWROW values

CREATE TRIGGER BUG_UPDATE_TRIGGER
AFTER UPDATE OF STATUS, PRIORITY ON BUG_INFO
REFERENCING OLDROW, NEWROW
FOR EACH ROW 
 
IMPORT  
import java.sql.* ;
BEGIN
try 
{     
     // column number of STATUS is 10
     String  old_status, new_status;
     old_status = (String) OLDROW.getValue(10, CHAR);
     new_status = (String) NEWROW.getValue(10, CHAR);
     if ((old_status.CompareTo("OPEN") == 0) && 
     (new_status.CompareTo("FIXED") == 0))
     {
  //  If STATUS has changed from OPEN to FIXED
  //  increment the bugs_fixed_cnt by 1 in the 
  //  row corresponding to current month  
  //  and current year
     SQLIStatement  update_stmt (
     " update BUG_STATUS set bugs_fixed_cnt = bugs_fixed_cnt + 1 "
     " where  month = ?  and year = ?"
     );
          .
          .
          .